#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
delete from hive.edu_dwb.hkl_dwb_check_detail_wide where 1=1;
insert into hive.edu_dwb.hkl_dwb_check_detail_wide
-- 班级排课表
with cu as (select
class_id,
class_date,
content
from hive.edu_dwd.hkl_dim_course_upload
group by class_date,class_id, content),

-- 打卡表
 ss as (select
    student_id,
    class_id,
    time_table_id,
    signin_time,
    signin_date
from hive.edu_dwd.hkl_fact_student_signin
group by signin_time,
student_id,
class_id,
signin_date,
time_table_id) ,
-- 班级作息时间表
ct as (select id,
             class_id,
             morning_template_id,
             morning_begin_time,
             morning_end_time,
             afternoon_template_id,
             afternoon_begin_time,
             afternoon_end_time,
             evening_template_id,
             evening_begin_time,
             evening_end_time,
             use_begin_date,
             use_end_date
      from hive.edu_dwd.hkl_dim_class_time
      group by id,
               class_id,
               morning_template_id,
               morning_begin_time,
               morning_end_time,
               afternoon_template_id,
               afternoon_begin_time,
               afternoon_end_time,
               evening_template_id,
               evening_begin_time,
               evening_end_time,
               use_begin_date,
               use_end_date),
-- 请假表
sl as (select
class_id,
student_id,
audit_state,
leave_type,
begin_time,
begin_time_type,
end_time,
end_time_type,
days,
cancel_state,
cancel_time,
old_leave_id,
valid_state
from hive.edu_dwd.hkl_fact_student_leave
)
-- 班级排课 关联 请假表 关联 作息时间
select date_format(current_date,'%Y-%m-%d') as create_date,
       cu.class_id as class_id,
       cu.class_date,
       sl.student_id as student_id,
       null as signin_time,             --打卡表
       null as signin_date,             --打卡表
       begin_time,
       begin_time_type,
       end_time,
       end_time_type,
       days,
       morning_template_id,
       morning_begin_time,
       morning_end_time,
       afternoon_template_id,
       afternoon_begin_time,
       afternoon_end_time,
       evening_template_id,
       evening_begin_time,
       evening_end_time,
       use_begin_date,
       use_end_date
from sl
left join ct
on sl.class_id = ct.class_id
inner join cu
on  cu.class_id=sl.class_id
where (class_date between use_begin_date and use_end_date) and ct.class_id is not null
and cu.class_date>=substring (begin_time ,1,10) and cu.class_date<=substring (end_time,1,10)
union all
select date_format(current_date,'%Y-%m-%d') as create_date,
       cu.class_id ,
       cu.class_date,
       ss.student_id,
       signin_time,             --打卡表
       signin_date,             --打卡表
       null as begin_time,               --请假表
       null as begin_time_type,
       null as end_time,
       null as end_time_type,
       null as days,
       morning_template_id,     --作息表
       morning_begin_time,
       morning_end_time,
       afternoon_template_id,
       afternoon_begin_time,
       afternoon_end_time,
       evening_template_id,
       evening_begin_time,
       evening_end_time,
       use_begin_date,
       use_end_date             --作息表
from cu
left join ss
       on cu.class_id=ss.class_id
       and cu.class_date=ss.signin_date
left join ct
       on ct.id=ss.time_table_id
where ct.class_id is not null;
"